import time
import datetime
import collections
from django.shortcuts import render
from django.http import JsonResponse
from django.db.models import Count
from django.db.models import Count
from web import models


# from django.db.models import Count
# from django.db.models.functions import TruncDate
# from datetime import timedelta

def dashboard(request, project_id):
    status_dict = collections.OrderedDict()  # 构造有序字典
    for key, text in models.Issues.status_choices:
        status_dict[key] = {'text': text, 'count': 0}
    # 按条件对他进行分组聚合
    # 聚合每个状态的问题个数形成列表
    issues_data = models.Issues.objects.filter(project_id=project_id).values('status').annotate(ct=Count('id'))

    # 更新每个状态的数量
    for item in issues_data:
        status_dict[item["status"]]['count'] = item["ct"]
    # 获取项目成员
    # user_object = models.ProjectUser.objects.filter(project=request.tracer.project).select_related('user')
    # user_list = [{'user_id':item.user.id,'user__username':item.user.username} for item in user_object]
    # print(user_list)

    # QuerySet 支持链式调用（如 .filter()、.exclude()、.order_by() 等），并且可以延迟执行（即只有在真正需要数据时才会执行数据库查询）。
    user_list = models.ProjectUser.objects.filter(project_id=project_id).values('user_id',
                                                                                'user__username')  # QuerySet类型
    # 获取前十个任务
    top_ten_object = models.Issues.objects.filter(project_id=project_id, assign__isnull=False).order_by('-id')[0:10]
    context = {
        'status_dict': status_dict,
        'user_list': user_list,
        'top_ten_object': top_ten_object
    }
    return render(request, 'dashboard.html', context)


def issues_chart(request, project_id):
    """ 在概览页面生成highcharts所需的数据 """
    #获取当天的时间 2025-02-28
    today = datetime.datetime.now().date()
    #创建有序字典
    date_dict = collections.OrderedDict()
    #倒退30天获取时间和时间戳，初始化任务数为0
    # {
    #     '2025-02-08':[1740672000000.0, 0],
    #     '2025-02-27':[1740585600000.0, 0]
    # }
    for i in range(0, 30):
        date = today - datetime.timedelta(days=i)
        date_dict[date.strftime("%Y-%m-%d")] = [time.mktime(date.timetuple()) * 1000, 0]


    # select xxxx,1 as ctime from xxxx
    # select id,name,email from table;
    # select id,name, strftime("%Y-%m-%d",create_datetime) as ctime from table;
    # "DATE_FORMAT(web_transaction.create_datetime,'%%Y-%%m-%%d')"
    #extra()额外多生成一个字段
    #获取每天的问题数目
    #<QuerySet [{'ctime': '2025-02-26', 'ct': 3}, {'ctime': '2025-02-27', 'ct': 3}]>
    #在查询中，create_datetime__date__gte 表示筛选 create_datetime 的日期部分大于或等于 thirty_days_ago 的记录。
    result = models.Issues.objects.filter(project_id=project_id,
                                          create_datetime__gte=today - datetime.timedelta(days=30)).extra(
        select={'ctime': "strftime('%%Y-%%m-%%d',web_issues.create_datetime)"}).values('ctime').annotate(ct=Count('id'))
    #将每天的问题数加到datelist里
    #OrderedDict([('2025-02-28', [1740672000000.0, 0]), ('2025-02-27', [1740585600000.0, 3]), ...]
    for item in result:
        date_dict[item['ctime']][1] = item['ct']
    return JsonResponse({'status': True, 'data': list(date_dict.values())})
